package db;

import java.io.IOException;
import java.net.Socket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


import nanoxml.XMLElement;
import utils.AbstractConnectionThread;

import commands.CommandBringStatistics;
import commands.CommandGameResult;
import commands.CommandNewUser;
import commands.CommandNewUserError;
import commands.CommandStatistics;
import commands.CommandUserNotVerified;
import commands.CommandUserLogon;
import commands.CommandUserVerified;
import commands.Commands;

/**
 * Handles connection between DB and the RPS game server
 */
public class ConnectionThreadDBToServer extends AbstractConnectionThread {
			
	private final static String TABLE_NAME = "UserInfo";
	private final static String FIELD_USERNAME = "username";
	private final static String FIELD_PASSWORD = "password";
	private final static String FIELD_NUM_OF_WINS = "winNum";
	private final static String FIELD_NUM_OF_LOSSES = "loseNum";
	
	
	public ConnectionThreadDBToServer (Socket clientSocket) {
		super (clientSocket);
	}

	public void init (String username) {
		System.out.print("this is the connection-thread\n");
	}	

	/**
	 * Opens a connection to the DB files.
	 * @return an open connection to the DB files.
	 */
	public Statement openCon () throws ClassNotFoundException, SQLException  {
		
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
		database += DBServer.getDBPath().trim() + ";DriverID=22;";
		Connection con = DriverManager.getConnection(database, "" , "" );
		Statement s = con.createStatement();
		return s;
	}
	
	private boolean checkUserExists(String username) {
		try {
			Statement s = openCon();
			s.execute("SELECT " + FIELD_USERNAME + " FROM " + TABLE_NAME + " WHERE " + FIELD_USERNAME + " = '"  + username + "'");
			ResultSet rs = s.getResultSet();
			if (!rs.next())
			{
				return false;
			}
			return true;
		}
		catch (Exception e) {
			System.out.println("Unable to access DB files.");
		}
		return false;
	}
	
	private boolean checkUser(String username, String password) {
		
		try {
			Statement s = openCon();
			s.execute("SELECT "+ FIELD_USERNAME + ", " + FIELD_PASSWORD + " FROM " + TABLE_NAME + " WHERE " + FIELD_USERNAME + " = '"  + username + "' and " + FIELD_PASSWORD + " = '" + password + "'");
			ResultSet rs = s.getResultSet();
			
			if (!rs.next()) {
				return false;
			}	
			return true;
		}
		catch (Exception e) {
			System.out.println("Unable to access DB files.");
		}
		return false;	
	}
	
	private void updateScores (String winUser, String loseUser) {
		try {
			Statement s = openCon();
			
			//Updating the user's wins number
			s.execute("SELECT " + FIELD_NUM_OF_WINS + " FROM " + TABLE_NAME + " WHERE " + FIELD_USERNAME + " = '" + winUser + "'");
			ResultSet rs = s.getResultSet();
			if (rs.next())
			{
				int res = rs.getInt(1);
				res++;
				s.execute("Update " + TABLE_NAME + " SET " + FIELD_NUM_OF_WINS + " = " + res + " WHERE " + FIELD_USERNAME + " = '" + winUser + "'" );
			}

			//Updating the user's lose number
			s.execute("SELECT " + FIELD_NUM_OF_LOSSES + " FROM " + TABLE_NAME + " WHERE " + FIELD_USERNAME + " = '" + loseUser + "'");
			rs = s.getResultSet();
			if (rs.next())
			{
				int res = rs.getInt(1);
				res++;
				s.execute("Update " + TABLE_NAME + " SET " + FIELD_NUM_OF_LOSSES + " = " + res + " WHERE " + FIELD_USERNAME + " = '" + loseUser + "'" );
			}
		}
		catch (Exception e) {
			System.out.println("Unable to access DB files.");
		}
	}
	
	private void addUser (String username, String password) {
		
		try {
			if (checkUserExists(username)) 
				send(new CommandNewUserError(username,password));
				else {
				Statement s = openCon();	
				//Updating the user's wins number
				s.execute("INSERT INTO " + TABLE_NAME + " (" + FIELD_USERNAME + ", " + FIELD_PASSWORD + ", "+ FIELD_NUM_OF_WINS + ", " + FIELD_NUM_OF_LOSSES + ") VALUES ( '" + username + "', '" + password + "', 0, 0)");
				send(new CommandNewUser(username,password));
			}
		}
		catch (Exception e) {
			System.out.println("Unable to access DB files.");
		}
	}
	
	private void bringStatistics (String user) {
		try {
			Integer wins = 0;
			Integer loses = 0;
			
			Statement s = openCon();	
			s.execute("SELECT " + FIELD_NUM_OF_WINS + " FROM " + TABLE_NAME + " WHERE " + FIELD_USERNAME + " = '" + user + "'");
			ResultSet rs = s.getResultSet();
			if (rs.next())
				wins = rs.getInt(1); 
				
			s.execute("SELECT " + FIELD_NUM_OF_LOSSES + " FROM " + TABLE_NAME + " WHERE " + FIELD_USERNAME + " = '" + user + "'");	
			rs = s.getResultSet();
			if (rs.next())
				loses = rs.getInt(1); 
			
			String wins_string = wins.toString();
			String loses_string = loses.toString();
			
			send(new CommandStatistics(user,wins_string,loses_string));
		}
		catch (Exception e) {
			System.out.println("Unable to access DB files.");
		}
	}

	public void parse (XMLElement command) {
		try	{
			if (command.getName().equals(Commands.USER_LOGON)) {
				CommandUserLogon commandUserLogon = new CommandUserLogon(command);
				username = commandUserLogon.getUsername();
				password = commandUserLogon.getPassword();
				if (checkUser(username,password)) {
					send(new CommandUserVerified(username));
				}
				else {
					send(new CommandUserNotVerified(username));
				}
			}
			else if (command.getName().equals(Commands.NEW_USER)) {
				CommandNewUser commandNewUser = new CommandNewUser(command);
				String user = commandNewUser.getUsername();
				String pass = commandNewUser.getPassword();
				addUser(user, pass);
			}
			else if (command.getName().equals(Commands.BRING_STATISTICS)) {
				CommandBringStatistics commandBringStatistics = new CommandBringStatistics(command);
				String user = commandBringStatistics.getUsername();
				bringStatistics(user);
			}	
			else if (command.getName().equals(Commands.GAME_RESULT)) {
				CommandGameResult commandGameResult = new CommandGameResult(command);
				String winUser =  commandGameResult.getWinnerName();
				String loseUser = commandGameResult.getLoserName(); 
				updateScores(winUser, loseUser);
			}
		}
		catch (Exception e) {
			System.out.println("Exceptiom from parse in ConnectionThreadDBToServer");
		}
	}
		
	public void handleDisconnection()
	{	
		try {
			socket.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
}
